package excelPa;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.function.Consumer;

/**
 * 完成解析逻辑模型导出文件，一键生成文件中所有表的DDL语句
 */
public class CreateDDLByLogicModel {

    public static void main(String[] args) throws IOException {
        String fileroot = "src/main/resources/食品安全逻辑模型.xlsx";
        readExcel(fileroot);
    }


    public static void readExcel(String fileroot) throws IOException {
        Map<String,String> name = new HashMap<>();
        Map<List,List> fileds_comment = new HashMap<>();
        Map<String,Map<List,List>> table_fields = new HashMap<>();
        FileInputStream in = new FileInputStream(fileroot);
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        XSSFSheet sheet = sheets.getSheet("逻辑模型表");
        int totalRows = sheet.getPhysicalNumberOfRows();
        int count = sheet.getRow(3).getRowNum();
        //System.out.println(totalRows);
        for (int i = 2; i < totalRows; i++) {
            String EtableName = sheet.getRow(i).getCell(2).toString();
            String CtableName = sheet.getRow(i).getCell(1).toString();
            //System.out.println(EtableName);
            name.put(CtableName,EtableName);
        }
        System.out.println("该文件的总表数："+name.size());

        List<String> fields = new ArrayList<>();
        List<String> comments = new ArrayList<>();
        String tableName;
        String  etableName;
        String tmpTableName=sheet.getRow(2).getCell(1).toString();
        StringBuilder sb = new StringBuilder();
        for (int i = 2; i < totalRows; i++) {
                tableName = sheet.getRow(i).getCell(1).toString();
                etableName = sheet.getRow(i).getCell(2).toString();
                if (!tableName.equals(tmpTableName)) {
                    //生成DDL
                    createDDLsql(etableName,sb,fields,comments,tmpTableName);
                    //清空sb
                    sb.delete(0,sb.length());
                    fields.clear();
                    comments.clear();
                    tmpTableName = tableName;
                    i--;
                } else {
                    String field = sheet.getRow(i).getCell(6).toString();
                    String comment = sheet.getRow(i).getCell(8).toString();
                    //对应表的每一行添加入两个列表
                    fields.add(field);
                    comments.add(comment);
                    if(i==totalRows-1){
                        createDDLsql(etableName,sb,fields,comments,tmpTableName);
                        sb.delete(0,sb.length());
                        fields.clear();
                        comments.clear();
                    }
                }

        }


    }


    public static void createDDLsql(String etableName,StringBuilder sb,List<String> fields,List<String> comments,String tmpTableName) throws IOException {
        String sql1 ="create table "+etableName+" ( ";
        sb.append(sql1);
        for(int n=0;n<fields.size();n++){
            String sql2 =fields.get(n)+" string comment "+"'"+comments.get(n)+"'"+",";
            if(n==fields.size()-1){
                String[] split = sql2.split(",");
                //System.out.println(split[0]);
                sb.append(split[0]);
            }else {
                sb.append(sql2);
            }
        }
        String sql3 = ") comment "+"'"+tmpTableName+"'"+";";
        sb.append(sql3);
        System.out.println(sb.toString());
    }

}
